package com.lingyun.ucm.web.mvc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.lingyun.ucm.common.pojo.bean.ClientReport;
/**
 * 
 * @author Terrence2013
 * 2018年1月24日 上午9:53:06
 * 客户端接入情况Dao
 */
public class ClientReportDao{

    /**
     * 创建client_report表
     *
     * @return
     */
    public static boolean createTable() {
        Statement statement = null;
        Connection conn = null;
        boolean result = false;
        try {
            conn = H2Util.getConnection();
            conn.setAutoCommit(false);
            statement = conn.createStatement();
            statement.execute("DROP TABLE IF EXISTS CLIENT_REPORT");
            result = statement.execute("CREATE TABLE CLIENT_REPORT(IP VARCHAR(30) PRIMARY KEY,FIRST_TIME DATETIME,LAST_TIME DATETIME)");
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 插入记录
     *
     * @param clientReport
     * @return
     */
    public int insert(ClientReport clientReport) {
        PreparedStatement statement = null;
        Connection conn = null;
        int result = 0;
        try {
            conn = H2Util.getConnection();
            conn.setAutoCommit(false);
            statement = conn.prepareStatement("INSERT INTO CLIENT_REPORT VALUES (?,?,?)");
            statement.setString(1, clientReport.getIp());
            statement.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
            statement.setTimestamp(3, new Timestamp(new java.util.Date().getTime()));
            result = statement.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 更新记录
     *
     * @param clientReport
     * @return
     */
    public int update(ClientReport clientReport) {
        PreparedStatement statement = null;
        Connection conn = null;
        int result = 0;
        try {
            conn = H2Util.getConnection();
            conn.setAutoCommit(false);
            statement = conn.prepareStatement("UPDATE CLIENT_REPORT SET LAST_TIME = ? WHERE IP = ?");
            statement.setTimestamp(1, new Timestamp(clientReport.getLast_time().getTime()));
            statement.setString(2, clientReport.getIp());
            result = statement.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 查找单个记录
     *
     * @param ip
     * @return
     */
    public ClientReport get(String ip) {
        PreparedStatement statement = null;
        Connection conn = null;
        ClientReport clientReport = null;
        ResultSet resultSet = null;
        try {
            conn = H2Util.getConnection();
            statement = conn.prepareStatement("SELECT * FROM CLIENT_REPORT WHERE IP = ? ");
            statement.setString(1, ip);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                clientReport = new ClientReport();
                clientReport.setIp(resultSet.getString(1));
                clientReport.setFirst_time(resultSet.getTimestamp(2));
                clientReport.setLast_time(resultSet.getTimestamp(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return clientReport;
    }

    /**
     * 查找所有记录
     *
     * @return
     */
    public List<ClientReport> listAll() {
        PreparedStatement statement = null;
        Connection conn = null;
        ResultSet resultSet = null;
        List<ClientReport> reportList = new ArrayList<>();
        try {
            conn = H2Util.getConnection();
            statement = conn.prepareStatement("SELECT * FROM CLIENT_REPORT");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ClientReport clientReport = new ClientReport();
                clientReport.setIp(resultSet.getString(1));
                clientReport.setFirst_time(resultSet.getTimestamp(2));
                clientReport.setLast_time(resultSet.getTimestamp(3));
                reportList.add(clientReport);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return reportList;
    }


    public static void main(String[] args) {
//        clientReport bean = new clientReport();
//        bean.setFirst_time(new java.util.Date());
//        bean.setLast_time(new java.util.Date());
//        bean.setIp("127.0.0.1");
//        int insert = new ClientReportDao().insert(bean);
//        System.out.println(insert);
        List<ClientReport> clientReports = new ClientReportDao().listAll();
        System.out.println(clientReports.size());
        for (ClientReport clientReport : clientReports) {
            System.out.println(clientReport.getIp());
        }
    }

}
